﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using System.Data.OleDb;
using System.IO;
namespace ModuleDuoc
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();
        }

        private void frmNhapHang_Click(object sender, EventArgs e)
        {
            frmHD_NhapHang frm = new frmHD_NhapHang("Nguyễn Thế Sơn", "02", "HDNH");
            frm.ShowDialog();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            string sql_u = "select * from TabProducts";
            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString04"], CommandType.Text, sql_u).Tables[0];
            
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (dgvData.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string sql = "INSERT INTO zD_DM_THUOC ";
                    sql += "([MAVT]";
                    sql += ",[MAHANGHOA]";
                    sql += ",[TENVT]";
                    sql += ",[MANHOMDV]";
                    sql += ",[MANHOMVATTU]";
                    sql += ",[DVT]";
                    sql += ",[THUOCTINHDONGGOI]";
                    sql += ",[MANSX]";
                    sql += ",[TONTOIDA]";
                    sql += ",[TONTOITHIEU]";
                    sql += ",[VITRILUUTRU]";
                    sql += ",[GHICHU]";
                    sql += ",[CHINHANH_DB]";
                    sql += ",[NHACC]";
                    sql += ",[Infoinclude]";
                    sql += ",[Usage]";
                    sql += ",[NameSearch])";
                    sql += "VALUES";
                    sql += "('" + dgvData.Rows[i].Cells["PID"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["BarCode"].Value.ToString() + "'";
                    if (dgvData.Rows[i].Cells["P_Name"].Value.ToString().IndexOf('(') > 0)
                    {
                        int cut = dgvData.Rows[i].Cells["P_Name"].Value.ToString().Substring(dgvData.Rows[i].Cells["P_Name"].Value.ToString().IndexOf('(')).Length;
                        sql += ",N'" + dgvData.Rows[i].Cells["P_Name"].Value.ToString().Replace(",", ".").Substring(0,dgvData.Rows[i].Cells["P_Name"].Value.ToString().Length - cut)  + "'";
                    }
                    else
                    {
                        sql += ",N'" + dgvData.Rows[i].Cells["P_Name"].Value.ToString().Replace(",", ".") + "'";
                    }
                    sql += ",''";
                    sql += ",'" + dgvData.Rows[i].Cells["GpID"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["P_DonViTinh"].Value.ToString() + "'";
                    sql += ",N'Hộp'";
                    sql += ",'XX'";
                    sql += ",'" + dgvData.Rows[i].Cells["MaxBalance"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["MinBalance"].Value.ToString() + "'";
                    sql += ",'1'";
                    sql += ",N''";
                    sql += ",'100'";
                    sql += ",N'Nhà cc'";
                    sql += ",N'" + dgvData.Rows[i].Cells["InfoInclude"].Value.ToString().Replace(",",".") + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["Usage"].Value.ToString().Replace(",",".") + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["NameSearch"].Value.ToString().Replace(",",".") + "')";

                    SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DM.DM_DM_THUOC frm = new ModuleDuoc.DM.DM_DM_THUOC();
            frm.ShowDialog();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql_u = "select * from TabUnitProduct";
            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString04"], CommandType.Text, sql_u).Tables[0];
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (dgvData.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string sql = "INSERT INTO [zD_DM_DVT] ";
                    sql += "([Ma]";
                    sql += ",[Ten]";
                    sql += ",[Ghichu]) ";
                    sql += "VALUES";
                    sql += "('" + dgvData.Rows[i].Cells["UnitID"].Value.ToString() + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["UnitName"].Value.ToString() + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["UnitNotes"].Value.ToString() + "')";
                    SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            string sql_u = "select * from TabGproducts";
            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString04"], CommandType.Text, sql_u).Tables[0];
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (dgvData.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string sql = "INSERT INTO [zD_DM_VATTU_NHOMVATTU] ";
                    sql += "([MANHOM]";
                    sql += ",[TENNHOM]";
                    sql += ",[GHICHU]) ";
                    sql += "VALUES";
                    sql += "('" + dgvData.Rows[i].Cells["GpID"].Value.ToString() + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["GpName"].Value.ToString() + "'";
                    sql += ",N'" + dgvData.Rows[i].Cells["GpNote"].Value.ToString() + "')";
                    SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
                }
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            frmTK_NhapHangcs frm = new frmTK_NhapHangcs();
            frm.ShowDialog();
        }

        private void frmMain_Load(object sender, EventArgs e)
        {

        }

        private void button8_Click(object sender, EventArgs e)
        {
            frmHD_BanLe frm = new frmHD_BanLe();
            frm.Show();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            frmHD_NhapHang frm = new frmHD_NhapHang("Nguyễn Thế Sơn", "02", "HDKK");
            frm.ShowDialog();
        }

        private void button10_Click(object sender, EventArgs e)
        {
            Report.frmTK_XuatNhap frm = new ModuleDuoc.Report.frmTK_XuatNhap();
            frm.ShowDialog();
        }

        private void button11_Click(object sender, EventArgs e)
        {
            frmTK_XuatHang frm = new frmTK_XuatHang();
            frm.ShowDialog();
        }

        private void button12_Click(object sender, EventArgs e)
        {
            Report.frmTK_TonKho frm = new ModuleDuoc.Report.frmTK_TonKho();
            frm.ShowDialog();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            frmDonThuoc frm = new frmDonThuoc();
            frm.ShowDialog();
        }

        private void button14_Click(object sender, EventArgs e)
        {
            string sql_u = "select * from TabPartner";
            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString04"], CommandType.Text, sql_u).Tables[0];
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                string sql = "INSERT INTO [zD_NHACUNGCAP]";
                sql += "([MANCC]";
                sql += ",[TENNCC]";
                sql += ",[DIACHI]";
                sql += ",[SODT]";
                sql += ",[SOFAX]";
                sql += ") values(";
                sql += "'" + dgvData.Rows[i].Cells["CusID"].Value.ToString() + "',";
                sql += "N'" + dgvData.Rows[i].Cells["Name"].Value.ToString() + "',";
                sql += "N'" + dgvData.Rows[i].Cells["Address"].Value.ToString() + "',";
                sql += "N'" + dgvData.Rows[i].Cells["Phone"].Value.ToString() + "',";
                sql += "N'" + dgvData.Rows[i].Cells["Fax"].Value.ToString() + "'";
                sql += ")";
                SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
            }
        }

        private void button16_Click(object sender, EventArgs e)
        {
            string sqls = "select * from vw_NHAPXUAT where TypeInput = 'IN1'";
            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString04"], CommandType.Text, sqls).Tables[0];
            if (dgvData.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string sql = "INSERT INTO [zD_PHIEUNHAPKHOCT]";
                    sql += "([sophieu]";
                    sql += ",[mavt]";
                    sql += ",[mavach]";
                    sql += ",[solo]";
                    sql += ",[soluong]";
                    sql += ",[dongianhap]";
                    sql += ",[vat]";
                    sql += ",[thanhtien]";
                    //sql += ",[ngaysx]";
                    //sql += ",[hansd]";
                    sql += ",[sodutru]";
                    sql += ",[nhacc]";
                    sql += ",[hopdong]";
                    sql += ",[baogia]";
                    sql += ",[stt]";
                    sql += ",[ghichu]";
                    sql += ",[giaban],[source])";
                    sql += " VALUES";
                    sql += "('" + dgvData.Rows[i].Cells["billID"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["Pid"].Value.ToString() + "'";
                    sql += ",'" + i + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["Packet"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["productNumber"].Value.ToString() + "'";
                    sql += ",'" + dgvData.Rows[i].Cells["productPrice"].Value.ToString().Replace(",",".") + "'";
                    sql += ",'0'";
                    sql += ",'" + dgvData.Rows[i].Cells["productMoney"].Value.ToString().Replace(",", ".") + "'";
                    //sql += ",'" + dgvData.Rows[i].Cells["productDate"].Value.ToString() + "'";
                    //sql += ",'" + dgvData.Rows[i].Cells["ProductExpiryDate"].Value.ToString() + "'";
                    sql += ",''";
                    sql += ",''";
                    sql += ",''";
                    sql += ",''";
                    sql += ",''";
                    sql += ",''";
                    sql += ",'" + dgvData.Rows[i].Cells["PublicPrice"].Value.ToString().Replace(",", ".") + "','old')";

                    SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
                }
            }
        }

        private void button17_Click(object sender, EventArgs e)
        {
            frmNiemYetGia frm = new frmNiemYetGia();
            frm.ShowDialog();
        }
        private DataTable LoadSpreadsheet(string filename, bool hasHeader)
        {
            try
            {
                DataTable table = new DataTable("Table_Name");
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=" + (hasHeader ? "Yes" : "No") + ";IMEX=1\";";
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    using (OleDbCommand command = new OleDbCommand())
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            command.Connection = connection;
                            command.CommandText = "select mavach,tenvt,quocgia,dvt,hansd,soluong,gianhap,thanhtien,giaban,madvt,mavt from [T9$]";
                            adapter.Fill(table);
                        }
                    }
                    connection.Close();
                }
                return table;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        private void button18_Click(object sender, EventArgs e)
        {
            dgvData.AutoGenerateColumns = false;
            string stFileName = null;

            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = System.Environment.CurrentDirectory;
            openFileDialog1.Title = "Open Text File";
            openFileDialog1.Filter = "Text files (*.xls)|*.xls";
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string stFilePathAndName = openFileDialog1.FileName;
                FileInfo MyFile = new FileInfo(stFilePathAndName);
                stFileName = MyFile.Name;

                MessageBox.Show("Your File Info: " + Environment.NewLine + Environment.NewLine + "File Path and Name: " + stFilePathAndName + Environment.NewLine + Environment.NewLine + "File Name: " + stFileName, "File Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.dgvData.DataSource = LoadSpreadsheet(stFilePathAndName, true);
            }
        }

        private void button19_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                try
                {
                    string sql = "select dongianhap,giaban,solo,tenvt,dvt as madvt,zD_DM_THUOC.mavt";
                    sql += " from zD_PHIEUNHAPKHOCT ";
                    sql += " inner join zD_DM_THUOC";
                    sql += " on zD_DM_THUOC.mavt = zD_PHIEUNHAPKHOCT.mavt where tenvt =N'" + dgvData.Rows[i].Cells["tenvt"].Value.ToString().Trim().Replace("'","") + "' order by sophieu ";
                    DataTable dt = new DataTable();
                    dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        float gianhapgannhat = float.Parse(dt.Rows[0]["dongianhap"].ToString());
                        float giabangannhat = float.Parse(dt.Rows[0]["giaban"].ToString());
                        float giatong = int.Parse(dgvData.Rows[i].Cells["soluong"].Value.ToString()) * gianhapgannhat;
                        try
                        {
                            dgvData.Rows[i].Cells["thanhtien"].Value = giatong.ToString();
                        }
                        catch
                        {
                            dgvData.Rows[i].Cells["thanhtien"].Value = "0";
                        }
                        try
                        {
                            dgvData.Rows[i].Cells["gianhap"].Value = gianhapgannhat.ToString();
                        }
                        catch
                        {
                            dgvData.Rows[i].Cells["gianhap"].Value = "0";
                        }
                        try
                        {
                            dgvData.Rows[i].Cells["giaban"].Value = giabangannhat.ToString();
                        }
                        catch
                        {
                            dgvData.Rows[i].Cells["giaban"].Value = "0";
                        }
                        try
                        {
                            dgvData.Rows[i].Cells["madvt"].Value = dt.Rows[0]["madvt"].ToString();
                        }
                        catch
                        {
                            dgvData.Rows[i].Cells["madvt"].Value = "0";
                        }
                        try
                        {
                            dgvData.Rows[i].Cells["mavt"].Value = dt.Rows[0]["mavt"].ToString();
                        }
                        catch {
                            dgvData.Rows[i].Cells["mavt"].Value = "0";
                        }
                    }
                }
                catch { }
            }
        }

        private void button20_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                string sql = "INSERT INTO [zD_PHIEUNHAPKHOCT]";
                sql += "([sophieu]";
                sql += ",[mavt]";
                sql += ",[mavach]";
                sql += ",[solo]";
                sql += ",[soluong]";
                sql += ",[dongianhap]";
                sql += ",[vat]";
                sql += ",[thanhtien]";
                //sql += ",[ngaysx]";
                sql += ",[hansd]";
                sql += ",[sodutru]";
                sql += ",[nhacc]";
                sql += ",[hopdong]";
                sql += ",[baogia]";
                sql += ",[stt]";
                sql += ",[ghichu]";
                sql += ",[giaban],[source])";
                sql += " VALUES";
                sql += "('HDNN-05-10-2012'";
                sql += ",'" + dgvData.Rows[i].Cells["mavt"].Value.ToString() + "'";
                sql += ",'" + dgvData.Rows[i].Cells["mavach"].Value.ToString() + "'";
                sql += ",'0000'";
                sql += ",'" + dgvData.Rows[i].Cells["soluong"].Value.ToString() + "'";
                sql += ",'" + dgvData.Rows[i].Cells["gianhap"].Value.ToString().Replace(",", ".") + "'";
                sql += ",'0'";
                sql += ",'" + dgvData.Rows[i].Cells["thanhtien"].Value.ToString().Replace(",", ".") + "'";
                //sql += ",'" + dgvData.Rows[i].Cells["productDate"].Value.ToString() + "'";
                try
                {
                    sql += ",'" + DateTime.Parse(dgvData.Rows[i].Cells["hansd"].Value.ToString()).ToString("MM/dd/yyyy") + "'";
                }
                catch
                {
                    sql += ",'" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
                }

                sql += ",''";
                sql += ",''";
                sql += ",''";
                sql += ",''";
                sql += ",''";
                sql += ",''";
                sql += ",'" + dgvData.Rows[i].Cells["giaban"].Value.ToString().Replace(",", ".") + "','old')";

                SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql);
            }
        }

        private void button21_Click(object sender, EventArgs e)
        {
            frmDonThuoc frm = new frmDonThuoc();
            frm.ShowDialog();
        }

        private void button22_Click(object sender, EventArgs e)
        {
            DM.DM_NHACC frm = new ModuleDuoc.DM.DM_NHACC();
            frm.ShowDialog();
        }

        private void button23_Click(object sender, EventArgs e)
        {
            DM.frmDM_TheKho frm = new ModuleDuoc.DM.frmDM_TheKho();
            frm.ShowDialog();
        }

        private void button24_Click(object sender, EventArgs e)
        {
            frmTK_LaiLo frm = new frmTK_LaiLo();
            frm.ShowDialog();
        }

        private void button25_Click(object sender, EventArgs e)
        {
            frmBaoCaoKiemNhap frm = new frmBaoCaoKiemNhap();
            frm.ShowDialog();
        }

        private void button26_Click(object sender, EventArgs e)
        {
            frmKiemKe frm = new frmKiemKe();
            frm.ShowDialog();
        }

        private void button27_Click(object sender, EventArgs e)
        {
            Report.frmTK_ThuocTralai_New frm = new Report.frmTK_ThuocTralai_New();
            frm.ShowDialog();
        }

        private void button28_Click(object sender, EventArgs e)
        {
            frmDuyetTraLai frm = new frmDuyetTraLai();
            frm.ShowDialog();
        }

        private void button29_Click(object sender, EventArgs e)
        {
            frmTK_DoanhThu frm = new frmTK_DoanhThu();
            frm.Show();
        }

        private void button30_Click(object sender, EventArgs e)
        {
            frmTK_XuatHang frm = new frmTK_XuatHang();
            frm.Show();
        }

        private void button32_Click(object sender, EventArgs e)
        {
            frmTK_NhapHangcs f = new frmTK_NhapHangcs();
            f.Show();
        }

        private void button33_Click(object sender, EventArgs e)
        {
            frmTK_XuatHang f = new frmTK_XuatHang();
            f.Show();
        }

        private void button34_Click(object sender, EventArgs e)
        {
            frmTK_DoanhThu f = new frmTK_DoanhThu();
            f.Show();
        }

        private void button37_Click(object sender, EventArgs e)
        {
            Report.frmTK_ThuocTralai_New f = new ModuleDuoc.Report.frmTK_ThuocTralai_New();
            f.Show();
        }
        private void button39_Click(object sender, EventArgs e)
        {
            Report.frmTK_XuatNhap f = new ModuleDuoc.Report.frmTK_XuatNhap();
            f.Show();
        }

        private void button35_Click(object sender, EventArgs e)
        {
            Report.frmTK_BienBanKiemKe f = new ModuleDuoc.Report.frmTK_BienBanKiemKe();
            f.Show();
        }

        private void button36_Click(object sender, EventArgs e)
        {
            Report.frmTK_SuDungThuoc f = new ModuleDuoc.Report.frmTK_SuDungThuoc();
            f.Show();
        }

        private void button40_Click(object sender, EventArgs e)
        {
            Report.frmTK_TongHopTonKho_AT f = new ModuleDuoc.Report.frmTK_TongHopTonKho_AT();
            f.Show();
        }

        private void button41_Click(object sender, EventArgs e)
        {
            frmHD_BanLe f = new frmHD_BanLe();
            f.Show();
        }

        private void button42_Click(object sender, EventArgs e)
        {
            DM.DM_DM_THUOC f = new ModuleDuoc.DM.DM_DM_THUOC();
            f.Show();
        }

        private void button43_Click(object sender, EventArgs e)
        {
            frmDonThuoc f = new frmDonThuoc();
            f.Show();
        }

        private void button44_Click(object sender, EventArgs e)
        {
            DM.frmTheKho_New f = new ModuleDuoc.DM.frmTheKho_New();
            f.Show();
        }

        private void button45_Click(object sender, EventArgs e)
        {
            frmKiemKe f = new frmKiemKe();
            f.Show();
        }
    }
}
